package com.comcast.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.configuration.BaseConfiguration;
import org.apache.commons.configuration.PropertyConverter;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.comcast.util.ServerInfo;

/**
 * Support of storing configuration properties in a database.
 * Based on the commons-configuration DatabaseConfiguration class.
 * This has been modified to support the needs of the Comcast NTD organization
 *
 * <p>Supports default component version value ('ALL') that can be used to
 * provide default properties for all versions, to reduce redundant data as
 * well as to minimize maintenance effort for properties that won't change across
 * versions.
 *
 * <p>For services that already implement versioning adapter, it is recommended
 * to use default component version value because there is only one version and
 * thus no need to duplicate the configuration or update component version for
 * every new release.
 *
 * <p>Also supports reloading of database properties.
 *
 * @author Rick Engelfried
 * @author Charles Zhao
 */
public class DatabaseConfiguration extends BaseConfiguration {
	private static Log logger = LogFactory.getLog(DatabaseConfiguration.class);

    /** The datasource to connect to the database. */
    private DataSource datasource;

    /** Load Query */
    private static String QUERY_TEMPLATE =
    	"SELECT %d, PROP_KEY, PROP_VAL, CMPNT_NAME, CMPNT_VERSION, FARM_ID " +
    	"FROM CONFIG_PROPS " +
    	"WHERE CMPNT_NAME = ? " +
    	"AND   (CMPNT_VERSION = ? OR CMPNT_VERSION = 'ALL') " +
    	"%s " +
    	"AND   (FARM_ID = 0 OR FARM_ID=(SELECT FARM_ID FROM (SELECT FARM_ID FROM ESBGOODGUY.APP_SERVERS " +
    	"                              WHERE (APP_SERVER_HOST=? OR APP_SERVER_HOST='*') " +
    	"                                AND (APP_SERVER_NAME=? OR APP_SERVER_NAME='*') " +
    	"                              ORDER BY APP_SERVER_HOST DESC, APP_SERVER_NAME DESC) WHERE ROWNUM=1)) ";

    private static String PROP_REALOADABLE_CRITERIA = "AND   PROP_RELOADABLE = 'Y'";

    private static String ORDER_BY =
    	"ORDER BY 1, PROP_KEY, CMPNT_VERSION ASC, FARM_ID DESC";

    /** Tweaking Queries for Unit Test. Yeah, it is a hack.*/
    static {
    	if ( "true".equals(System.getProperty("DatabaseConfigurationTest"))) {
    		QUERY_TEMPLATE =  QUERY_TEMPLATE.replace("SELECT FARM_ID FROM (","").replace(") WHERE ROWNUM=1", " LIMIT 1").replace("ESBGOODGUY.","");
    	}
    }

    /** The component configuration: component name and version */
    private List<DBComponentConfiguration> dbComponentConfigs;

    /**
     * Build a configuration based on the component name and version. Loads all the properties.
     */
    public DatabaseConfiguration(DataSource datasource, List<DBComponentConfiguration> dbComponentConfigs) {
    	this(datasource, dbComponentConfigs, false);
    }

    /**
     * Build a configuration based on the component name and version. Only loads reloadable properties
     * when isReload is true.
     */
    public DatabaseConfiguration(DataSource datasource, List<DBComponentConfiguration> dbComponentConfigs, boolean isReload) {
        this.datasource = datasource;
        this.dbComponentConfigs = dbComponentConfigs;
        setLogger(logger);
        addErrorLogListener();  // log errors per default
        load(buildQuery(isReload));
    }

    private String buildQuery(boolean isReload) {
    	StringBuilder query = new StringBuilder();
    	for ( int i = 0; i < dbComponentConfigs.size(); i++) {
    		if ( i > 0 ) {
    			query.append("\nUNION ALL\n");
    		}
    		query.append(String.format(QUERY_TEMPLATE, i, (isReload ? PROP_REALOADABLE_CRITERIA : "")));
    	}
    	query.append("\n").append(ORDER_BY);

    	return query.toString();
    }

    /**
     * Load the properties from the table using the query passed in.
     */
    private void load(String query) {
    	executeConfigQuery(query, new ConfigQueryCallback(){
			public void processRow(ResultSet rs) throws SQLException {
				List<Object> results = new ArrayList<Object>();
            	String key = rs.getString(2).trim();
            	String strVal = rs.getString(3);
            	String componentName = rs.getString(4);
            	String componentVersion = rs.getString(5);
            	long farmId = rs.getLong(6);

                Object val = strVal == null ? null : strVal.trim();

                //If the key is already added, skip it. As a result, if multiple rows with the same key
                //are returned, only the first one will be added, and the result set has to be ordered
                //properly so that the closest matching value of a key will be returned first.
                if ( containsKey(key) ) {
                	return;
                }

            	if (isDelimiterParsingDisabled()) {
                	results.add(val);
                } else {
                	// Split value if it contains the list delimiter
                    CollectionUtils.addAll(results, PropertyConverter.toIterator(val, getListDelimiter()));
                }

                if (!results.isEmpty()) {
                	val = (results.size() > 1) ? results : results.get(0);
                }

                if (logger.isDebugEnabled()) {
                	logger.debug(String.format("Adding property from [%s : %s - Farm %d] - key = %s, value = %s", componentName, componentVersion, farmId, key, val));
                }

                boolean parsingDisabled = isDelimiterParsingDisabled();
                setDelimiterParsingDisabled(true);
                addProperty(key, val);

                setDelimiterParsingDisabled(parsingDisabled);
			}
    	});
    }

    private void executeConfigQuery(String query, ConfigQueryCallback callback) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            if (logger.isDebugEnabled()) {
            	logger.debug("Executing " + query);
            }
            // bind the parameters
            pstmt = conn.prepareStatement(query);

            for ( int i = 0; i < dbComponentConfigs.size(); i++) {
	            pstmt.setString(i * 4 + 1, this.dbComponentConfigs.get(i).getName());
	            pstmt.setString(i * 4 + 2, this.dbComponentConfigs.get(i).getVersion());
	            pstmt.setString(i * 4 + 3, ServerInfo.getPhysicalHostName());
	            pstmt.setString(i * 4 + 4, ServerInfo.getWlsServerName());
            }

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
            	callback.processRow(rs);
            }
        } catch (SQLException e) {
            fireError(EVENT_READ_PROPERTY, null, null, e);
            throw new RuntimeException("Error loading database properties: " + dbComponentConfigs, e);
        } finally {
            closeQuietly(conn, pstmt);
        }
    }

    /**
     * Returns the used <code>DataSource</code> object.
     *
     * @return the data source
     * @since 1.4
     */
    public DataSource getDatasource()
    {
        return datasource;
    }

    /**
     * Returns a <code>Connection</code> object. This method is called when
     * ever the database is to be accessed. This implementation returns a
     * connection from the current <code>DataSource</code>.
     *
     * @return the <code>Connection</code> object to be used
     * @throws SQLException if an error occurs
     * @since 1.4
     */
    protected Connection getConnection() throws SQLException
    {
        return getDatasource().getConnection();
    }

    /**
     * Close a <code>Connection</code> and, <code>Statement</code>.
     * Avoid closing if null and hide any SQLExceptions that occur.
     *
     * @param conn The database connection to close
     * @param stmt The statement to close
     */
    private void closeQuietly(Connection conn, Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			getLogger().warn(e.getMessage(), e);
		}

		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			getLogger().warn(e.getMessage(), e);
		}
	}

    private interface ConfigQueryCallback {
    	public void processRow(ResultSet resultSet) throws SQLException;
    }
}
